package com.team.hiring.common.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.team.hiring.common.entity.ApplicationEntity;
import com.team.hiring.common.entity.PageModel;
import com.team.hiring.common.util.DBUtil;

public class ApplicationDao {
	/**
	 * 查询申请信息
	 * @param companyId
	 * @return
	 */
	public List<ApplicationEntity> application(int companyId) {
		List<ApplicationEntity> applicationEntityList = new ArrayList<ApplicationEntity>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT p.name as 'position$name',t.name as 'talent$name',t.id as 'talent$id',a.app_time,a.status,a.id FROM company c,position p,application a,talent t WHERE"
					+ " c.id=p.company_id AND p.id=a.position_id AND a.talent_id=t.id AND  c.id=?;";
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, companyId);
			resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				ApplicationEntity applicationEntity = new ApplicationEntity();
				applicationEntity.setPosition$name(resultSet.getString("position$name"));
				applicationEntity.setTalent$name(resultSet.getString("talent$name"));
				applicationEntity.setTalent$id(resultSet.getInt("talent$id"));
				applicationEntity.setAppTime(resultSet.getString("a.app_time"));
				applicationEntity.setStatus(resultSet.getInt("a.status"));
				applicationEntity.setId(resultSet.getInt("a.id"));
				applicationEntityList.add(applicationEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, prepareStatement, connection);
		}
		return applicationEntityList;
	}

	
	
	/**
	 * 异步修改求审核状态
	 * @param request
	 * @param response
	 */
	public int updateStatus(int applicationId,int status) {
		int result = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		try {
			if(status==1){
				String sql = "update application a set a.status=1 WHERE a.id=?;";
				prepareStatement = connection.prepareStatement(sql);
				prepareStatement.setInt(1,applicationId);
			}else if(status==2){
				String sql = "update application a set a.status=2 WHERE a.id=?;";
				prepareStatement = connection.prepareStatement(sql);
				prepareStatement.setInt(1,applicationId);
			}
			int row = prepareStatement.executeUpdate();
			if (row != 0) {
				result = row;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(prepareStatement, connection);
		}
		return result;
	}


	/**
	 * 职位申请
	 * @param resumeId
	 * @param positionId
	 * @param talentId
	 * @return
	 */
	public int apply(int resumeId,int positionId,int talentId) {
		int result = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement prepareStatement = null;
		try {
			 String sql = "INSERT INTO application VALUES(NULL,?,?,?,3,NOW(),NULL)";
			 prepareStatement = connection.prepareStatement(sql);
			 prepareStatement.setInt(1,positionId);
			 prepareStatement.setInt(2,talentId);
			 prepareStatement.setInt(3,resumeId);
			 int row = prepareStatement.executeUpdate();
			 if (row !=0){
				 result = row;
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(prepareStatement, connection);
		}
		return result;
	}


	/**
	 *  申请职位
	 * @param positionId
	 * @param talentId
	 * @param resumeId
	 * @return
	 */
	public int applicationResume(int positionId, int talentId, int resumeId) {
		int row = 0;
		try {
			Connection connection = DBUtil.getConnection();
			String sql = "insert into application value(null,?,?,?,3,NOW(),NOW())";
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setInt(1, positionId);
			preparedStatement.setInt(2,talentId );
			preparedStatement.setInt(3, resumeId);
			int result = preparedStatement.executeUpdate();
			if(result!=0){
				row = result;
			}
			// 关闭连接
			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	
	
	/**
	 * 求职者查看申请状态
	 * 
	 * @param talentId
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageModel<ApplicationEntity> applicationTalent(int talentId,
			int pageNo, int pageSize) {

List<ApplicationEntity> applicationList = new ArrayList<>();
		
		try {
			Connection connection = DBUtil.getConnection();
			String sql = "	SELECT a.* ,p.name AS 'position$name',c.name AS 'company$name' FROM talent t,position p,application a,company c WHERE t.id=a.talent_id AND p.id=a.position_id AND c.id=p.company_id AND t.id=? limit ?,?";
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, talentId);
			prepareStatement.setInt(2,(pageNo-1)*pageSize );
			prepareStatement.setInt(3, pageSize);
			ResultSet resultSet = prepareStatement.executeQuery();
			while (resultSet.next()) {
				ApplicationEntity applicationEntity = new ApplicationEntity();
				applicationEntity.setId(resultSet.getInt("id"));
				applicationEntity.setPositionId(resultSet.getInt("position_id"));;
				applicationEntity.setResumeId(resultSet.getInt("resume_id"));
				applicationEntity.setTalentId(resultSet.getInt("talent_id"));
				applicationEntity.setStatus(resultSet.getInt("status"));
				applicationEntity.setAppTime(resultSet.getString("app_time"));
				applicationEntity.setHandleTime(resultSet.getString("handle_time"));
				applicationEntity.setPosition$name(resultSet.getString("position$name"));
				applicationEntity.setCompany$name(resultSet.getString("company$name"));
				applicationList.add(applicationEntity);
				System.out.println(applicationEntity.getHandleTime()+"时间");
				System.out.println(applicationEntity.getPosition$name());
			}
			
			//关闭连接
			resultSet.close();
			prepareStatement.close();
			connection.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return new PageModel<ApplicationEntity>(applicationList,getTotalRecords(),pageNo,pageSize);
	}

	
	
	
	
	/**
	 * 查询有多少条职位信息
	 * @return
	 */
	private int getTotalRecords(){
		int totalRecords = 0;
		try {
			//1、加载驱动
			//Class.forName("com.mysql.jdbc.Driver");
			//2、连接数据库
			//Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hiring", "root", "root");
			Connection connection = DBUtil.getConnection();
			//3、操作数据库
			String sql="select count(*) AS 'totalRecords' from application";
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				totalRecords = resultSet.getInt("totalRecords");
			}
			
			//4、关闭连接
			resultSet.close();
			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return totalRecords;
	}
	
}
